package service.db;

import com.google.gson.Gson;
import common.CustomException;
import java.sql.*;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import service.CommentObject;
import service.ImageJsonObject;
import service.ImageResource;

/**
 * Common class for database operations
 * 
 * @author Fredrik Kempe
 */
public class DatabaseOperations extends MySQL {

    /**
     * Craete a new entry for a image in the database
     * @param ijo the ImageJsonObject to create a entry from
     * 
     * @throws CustomException 
     */
    public static void saveNewImage(ImageJsonObject ijo) throws CustomException {
        openConnection();
        String sql = "CALL add_photo('"+ijo.getImg()+"', '"+ijo.getImg()+"', '"+ijo.getCaption()+"', '"+ijo.getLongitude()+"', '"+ijo.getLatitude()+"')";
        update(sql);
    }
    
    
    /**
     * creates a new entry for a comment to a photo
     * @param co a CommentObjecy
     * @param id the id of the photo
     * @throws CustomException 
     */
    public static void newComment(CommentObject co, int id) throws CustomException {
        openConnection();
        String sql = "CALL add_comment('"+co.getName()+"','"+co.getComment()+"',"+id+")";
        update(sql);
    }
    
    
    /**
     * get all images in the database
     * @return String, a json string with all the images and other information
     * @throws CustomException 
     */
    public static String getImages() throws CustomException {
        try {
            openConnection();
            String sql = "CALL get_photos()";
            ResultSet rs = query(sql);
            
            int numRes = numberOfResults(rs);
            
            if(numRes == 0)
                return "{'Error':'No images in database'}";
                

            String returnData = "[";
            int i=0;
            
            do {
                returnData += "{'ID':'";
                returnData += rs.getInt("photo_id");
                returnData += "', 'path':'";
                returnData += ImageResource.imageBaseUrl+rs.getString("photo_path");
                returnData += "', 'thumbnailPath':'";
                returnData += ImageResource.thumbnailBaseUrl+rs.getString("photo_path");
                returnData += "', 'numberOfComments':'";
                returnData += rs.getInt("number_of_comments");
                returnData += "', 'caption':'";
                
                if(numRes != ++i) {
                    returnData += rs.getString("caption")+"'},";
                } else
                    returnData += rs.getString("caption")+"'}";
            } while(rs.next());
            
            returnData += "]";
            return returnData;
            
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseOperations.class.getName()).log(Level.SEVERE, null, ex);
            throw new CustomException(ex.getMessage());
        }
    }
    
    
    /**
     * get all comments to a photo
     * @param id, photo id
     * @return String, a json string with all comments
     * @throws CustomException 
     */
    public static String getComments(int id) throws CustomException {
        try {
            openConnection();
            String sql = "CALL get_comments("+id+")";
            ResultSet rs = query(sql);
            
            int numRes = numberOfResults(rs);
            
            if(numRes == 0)
                return "{'Error':'No comments to photo'}";
                

            String returnData = "[";
            int i=0;
            
            do {
                returnData += "{'name':'";
                returnData += rs.getString("name");
                returnData += "', 'comment':'";
                
                if(numRes != ++i) {
                    returnData += rs.getString("commentText")+"'},";
                } else
                    returnData += rs.getString("commentText")+"'}";
            } while(rs.next());
            
            returnData += "]";
            return returnData;
            
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseOperations.class.getName()).log(Level.SEVERE, null, ex);
            throw new CustomException(ex.getMessage());
        }
    }
}
